package com.fowo.api.ptt.service.impl;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.db.Db;
import cn.hutool.db.Entity;
import cn.hutool.db.GlobalDbConfig;
import cn.hutool.db.Page;
import cn.hutool.db.sql.NamedSql;
import cn.hutool.extra.spring.SpringUtil;
import cn.hutool.log.level.Level;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.fowo.api.common.model.RException;
import com.fowo.api.common.query.SqlInjectionUtil;
import com.fowo.api.ptt.enitty.PrintTemplate;
import com.fowo.api.ptt.enitty.PrintTemplateType;
import com.fowo.api.ptt.mapper.PrintTemplateMapper;
import com.fowo.api.ptt.mapper.PrintTemplateTypeMapper;
import com.fowo.api.ptt.model.PrintTemplateLoadData;
import com.fowo.api.ptt.model.PrintTemplateLoadSql;
import com.fowo.api.ptt.service.PrintTemplateTypeService;
import com.fowo.api.user.model.JwtUserInfo;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.StringUtils;


import javax.annotation.Resource;
import javax.sql.DataSource;
import java.sql.SQLException;
import java.util.*;
import java.util.function.Function;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

/**
 * 打印类型服务实现
 *
 * @author yl_ls
 */
@Slf4j
@Service
public class PrintTemplateTypeServiceImpl implements PrintTemplateTypeService {

    static {
        GlobalDbConfig.setCaseInsensitive(false);
        GlobalDbConfig.setShowSql(true, false, true, Level.DEBUG);
    }

    @Resource
    private PrintTemplateTypeMapper typeMapper;
    @Resource
    private PrintTemplateMapper templateMapper;

    //用户替换
    public final String VAR_CURRENT_USER_ID_PARAM = "#{currentUserId}";

    @Transactional(rollbackFor = Exception.class)
    @Override
    public Long create(PrintTemplateType printTemplateType) {
        JwtUserInfo jwtUserInfo = JwtUserInfo.fromHeader();
        if (jwtUserInfo == null) {
            throw new RException("操作者身份必须提供");
        }
        if (null == printTemplateType.getLeaf()) {
            printTemplateType.setLeaf(false);
        }
        if (printTemplateType.getGroupId() == null) {
            throw new RException("必须设置类型编号");
        }
        if (!StringUtils.hasText(printTemplateType.getTypeName())) {
            throw new RException("必须设置模型类型名称");
        }
        Date now = new Date();
        printTemplateType.setCreateUser(jwtUserInfo.getUserId());
        printTemplateType.setCreateTime(now);
        printTemplateType.setLastUpdateUser(jwtUserInfo.getUserId());
        printTemplateType.setLastUpdateTime(now);
        printTemplateType.setDelFlag(false);

        if (typeMapper.insert(printTemplateType) != 1) {
            throw new RException("插入数据失败");
        }
        return printTemplateType.getId();
    }

    @Transactional(rollbackFor = Exception.class)
    @Override
    public void update(PrintTemplateType printTemplateType) {
        JwtUserInfo jwtUserInfo = JwtUserInfo.fromHeader();
        if (jwtUserInfo == null) {
            throw new RException("操作者身份必须提供");
        }
        PrintTemplateType rawTemplate = typeMapper.selectById(printTemplateType.getId());
        if (rawTemplate == null) {
            throw new RException("未找到要更新的打印类型");
        }

        Date now = new Date();
        printTemplateType.setLastUpdateUser(jwtUserInfo.getUserId());
        printTemplateType.setLastUpdateTime(now);

        if (typeMapper.updateById(printTemplateType) != 1) {
            throw new RException("更新数据失败");
        }
    }

    @Transactional(rollbackFor = Exception.class)
    @Override
    public void delete(Long id) {
        PrintTemplateType rawTemplate = typeMapper.selectById(id);
        if (rawTemplate == null) {
            throw new RException("未找到要更新的打印类型");
        }
        final List<PrintTemplate> templateList = templateMapper.selectByTypeId(id);
        if (CollUtil.isNotEmpty(templateList)) {
            throw new RException("打印类型下包含打印模板,请先删除!");
        }
        if (typeMapper.deleteById(id) != 1) {
            throw new RException("删除数据失败");
        }
    }


    /**
     * 执行sql
     *
     * @param printTemplateLoadSql
     * @return
     * @throws Exception
     */
    @Override
    public List<Entity> runSql(PrintTemplateLoadSql printTemplateLoadSql) throws Exception {
        DataSource dataSource = this.getDataSource();
        if (dataSource == null) {
            throw new Exception("数据源配置无效");
        }
        String sql = printTemplateLoadSql.getSql();
        Map<String, Object> paramMap = printTemplateLoadSql.getParams();
        //系统内置参数替换
        sql = this.systemParameter(sql, paramMap);
        //自定义参数替换
        sql = this.customizationParameter(sql, paramMap);
        SqlInjectionUtil.filterContent(sql);
        final NamedSql namedSql = convertToNamedSql(sql, paramMap); //三种参数占位方式 ':', '@', '?'
        List<Entity> query = Db.use(dataSource).page(namedSql.getSql(), new Page(), namedSql.getParams());
        log.info("sql:{},参数:{},结果:{}", sql, paramMap, query);
        return query;
    }

    /**
     * 将 #{} ${} 格式的参数应用的 SQL 并返回转换好的 SQL 和参数 信息
     * @param sql 查询
     * @param paramMap 参数信息
     */
    private NamedSql convertToNamedSql(String sql, Map<String, Object> paramMap) {
        Pattern pattern = Pattern.compile("([#$])\\{(.+)}");
        Matcher matcher = pattern.matcher(sql);
        StringBuilder sqlBuilder = new StringBuilder();
        Map<String, Object> namedParams = new HashMap<>();
        int parentNo = 1;
        while (matcher.find()) {
            if ("$".equals(matcher.group(1))) {
                // 原始 SQL 替换
                Object val = paramMap.get(matcher.group(2));
                if (val instanceof List) {
                    // 列表数据转换为 , 分隔格式文本
                    matcher.appendReplacement(sqlBuilder, ((List<?>) val).stream().map(Object::toString).collect(Collectors.joining(",")));
                } else {
                    matcher.appendReplacement(sqlBuilder, String.valueOf(val));
                }
            } else {
                // 参数化替换
                String paramName = String.format("p%d", parentNo++);
                namedParams.put(paramName, paramMap.get(matcher.group(2)));
                matcher.appendReplacement(sqlBuilder, ":" + paramName);
            }
        }
        matcher.appendTail(sqlBuilder);
        return new NamedSql(sqlBuilder.toString(), namedParams);
    }

    @Override
    public Object getPrintData(PrintTemplateLoadData templateLoadData) {
        PrintTemplateType printTemplateType = typeMapper.selectById(templateLoadData.getId());
        if (printTemplateType == null) {
            throw new RException("未找到指定的打印模版类型");
        }
        if (printTemplateType.getData() == null) {
            throw new RException("打印模版类型未完成配置");
        }
        JSONObject loadData = printTemplateType.getData().getJSONObject("loadData");
        JSONArray dataSources = printTemplateType.getData().getJSONArray("dataSources");
        if (loadData == null) {
            throw new RException("打印模版类型未完成配置");
        }
        if ("sql".equals(loadData.getString("type"))) {
            try {
                return getPrintDataForSql(loadData, dataSources, templateLoadData.getParams());
            } catch (SQLException e) {
                log.error("打印模版类型 {}，参数 {} 查询错误", templateLoadData.getId(), templateLoadData.getParams(), e);
                throw new RException("查询发生 SQL 错误，详情请查看系统日志");
            }
        }
        throw new RException("当前不支持的数据源类型");
    }

    private Object getPrintDataForSql(JSONObject loadData, JSONArray dataSources, Map<String, Object> params) throws SQLException {
        JSONArray sqlsJson = loadData.getJSONArray("sqls");
        if (sqlsJson == null || sqlsJson.isEmpty()) {
            throw new RException("打印模版类型 SQL 数据源未定义");
        }
        List<PrintTemplateLoadSql> loadSqlList = sqlsJson.toJavaList(PrintTemplateLoadSql.class);
        Map<String, List<Entity>> resultMap = new HashMap<>();
        Map<String, JSONObject> dataSourceMap = new HashMap<>();
        Map<String, PrintTemplateLoadSql> loadSqlMap = new HashMap<>();
        JSONObject mainDataSource = null;
        for (PrintTemplateLoadSql printTemplateLoadSql : loadSqlList) {
            if(!StringUtils.hasText(printTemplateLoadSql.getSql())) {
                continue;
            }
            JSONObject dataSource = jsonArrayFind(dataSources, i -> Objects.equals(printTemplateLoadSql.getDataSourcesId(), i.getString("id")));
            if (dataSource == null) {
                continue;
            }
            loadSqlMap.put(printTemplateLoadSql.getDataSourcesId(), printTemplateLoadSql);
            resultMap.put(printTemplateLoadSql.getDataSourcesId(), getPrintDataForLoadSql(printTemplateLoadSql, params));
            dataSourceMap.put(printTemplateLoadSql.getDataSourcesId(), dataSource);
            if (!Boolean.TRUE.equals(dataSource.getBoolean("isTable"))) {
                mainDataSource = dataSource;
            }
        }
        if (mainDataSource == null) {
            throw new RuntimeException("未找到可打印数据（没有主数据配置）");
        }
        List<Map<String, Object>> results = new ArrayList<>();
        // 先找到表头数据
        String mainId = mainDataSource.getString("id");
        List<Entity> mainEntities = resultMap.get(mainId);
        if (mainEntities != null) {
            for (Entity mainEntity : mainEntities) {
                Map<String, Object> result = new HashMap<>();
                result.put(mainId, mainEntity);
                results.add(result);
            }
            // 填充表体数据
            for (Map<String, Object> result : results) {
                for (Map.Entry<String, List<Entity>> resultMapEntity : resultMap.entrySet()) {
                    if (resultMapEntity.getKey().equals(mainId)) {
                        continue;
                    }
                    PrintTemplateLoadSql printTemplateLoadSql = loadSqlMap.get(resultMapEntity.getKey());
                    if (printTemplateLoadSql != null && StringUtils.hasText(printTemplateLoadSql.getMainField()) && StringUtils.hasText(printTemplateLoadSql.getSubField())) {
                        Entity mainRecord = (Entity)result.get(mainId);
                        Object mainFieldValue = mainRecord.get(printTemplateLoadSql.getMainField());
                        List<Entity> subRecords = resultMapEntity.getValue().stream().filter(c -> Objects.equals(mainFieldValue, c.get(printTemplateLoadSql.getSubField()))).collect(Collectors.toList());
                        result.put(resultMapEntity.getKey(), subRecords);
                    } else {
                        result.put(resultMapEntity.getKey(), resultMapEntity.getValue());
                    }
                }
            }
        }

        if (results.isEmpty()) {
            throw new RException("未找到可打印数据");
        }
        return results;
    }

    private JSONObject jsonArrayFind(JSONArray jsonArray, Function<JSONObject, Boolean> match) {
        for (int i = 0; i < jsonArray.size(); i++) {
            JSONObject jsonObject = jsonArray.getJSONObject(i);
            if (match.apply(jsonObject)) {
                return jsonObject;
            }
        }
        return null;
    }

    private List<Entity> getPrintDataForLoadSql(PrintTemplateLoadSql printTemplateLoadSql, Map<String, Object> params) throws SQLException {
        String sql = printTemplateLoadSql.getSql();
        // 参数替换
        sql = this.systemParameter(sql, params);
        sql = this.customizationParameter(sql, params);
        NamedSql namedSql = convertToNamedSql(sql, params);
        return Db.use(getDataSource()).query(namedSql.getSql(), namedSql.getParams());
    }

    /**
     * 获取数据源
     *
     * @return
     */
    private DataSource getDataSource() {
        return SpringUtil.getBean(DataSource.class);
    }


    /**
     * 系统参数替换
     *
     * @param sql
     * @param paramMap
     * @return
     */
    private String systemParameter(String sql, Map<String, Object> paramMap) {
        JwtUserInfo jwtUserInfo = JwtUserInfo.fromHeader();
        //用户替换
        if (StrUtil.contains(sql, VAR_CURRENT_USER_ID_PARAM)) {
            if (jwtUserInfo == null) {
                throw new RException("操作者身份必须提供");
            }
            sql = StrUtil.replace(sql, VAR_CURRENT_USER_ID_PARAM, StrUtil.concat(true, ":" + StrUtil.unWrap(VAR_CURRENT_USER_ID_PARAM, "#{", "}")), true);
            paramMap.put(StrUtil.unWrap(VAR_CURRENT_USER_ID_PARAM, "#{", "}"), jwtUserInfo.getUserId());
        }
        return sql;
    }


    /**
     * 自定义参数替换
     *
     * @param sql
     * @param paramMap
     * @return
     */
    private String customizationParameter(String sql, Map<String, Object> paramMap) {
        return sql;
    }
}
